-- ImpSoft Commerce Core PostgreSQL Table Definitions
-- ----------------------------------------------------------------------------
-- 
-- All tables within the application (other than those defined in plugins)
-- are defined in this file. This file is used to set up the application.
-- Eventually, this might be moved into like an /install directory, but for now
-- since there is no install process, creating tables will be done manually by
-- running this SQL file on the database.

-- ----------------------------------------------------------------------------

-- Users Table --
-- There is a helper function that needs to be added for certain things to work
-- called "sha1". The function also has a prerequisite, that you add the
-- contrib/pgcrypto cryptographic functions. You can find those here:

CREATE OR REPLACE FUNCTION sha1(bytea) RETURNS char(40) AS $$
BEGIN
	RETURN ENCODE(DIGEST($1, 'sha1'), 'hex');
END;
$$ LANGUAGE plpgsql;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
	user_id serial PRIMARY KEY,
	username text NOT NULL,
	password text NOT NULL,
	salt text NOT NULL,
	last_login timestamp default NULL,
	is_active boolean default 'n',
	user_role text NOT NULL default 'guest',
	email text NOT NULL UNIQUE,
	created timestamp NOT NULL,
	modified timestamp NOT NULL
);

-- Product Table --
-- The products table contains the price field even though the store will prob-
-- ably not end up using it. Price usually depends on a lot of factors, so
-- there will be at least a whole table dedicated to product pricing.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
	product_id serial PRIMARY KEY,
	title text NOT NULL,
	description text default NULL,
	cost numeric(10,2) NOT NULL,
	price numeric(10,2) NOT NULL,
	weight numeric(10,2) NOT NULL,
	sku text NOT NULL,
	upc text default NULL,
	created timestamp NOT NULL,
	modified timestamp NOT NULL
);

-- Category Table --
-- Eventually we'll use the lft and rgt columns (mptt), but for now I'm going with the standard parent/child stuff
DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
	category_id serial NOT NULL,
	"name" text NOT NULL,
	description text,
	parent_id integer,
	lft integer,
	rgt integer,
	is_active boolean NOT NULL,
	"order" integer,
	created timestamp without time zone NOT NULL,
	modified timestamp without time zone NOT NULL,
	CONSTRAINT categories_pkey PRIMARY KEY (category_id),
	CONSTRAINT categories_parent_id_fkey FOREIGN KEY (parent_id)
	REFERENCES categories (category_id) MATCH SIMPLE
	ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT categories_order_key UNIQUE ("order")
)


-- Product Category Table -- 
CREATE TABLE categories_products (
	categories_products_id serial PRIMARY KEY,
	category_id integer REFERENCES categories(category_id),
	product_id integer REFERENCES products(product_id)
)

-- Cart Table -- 
DROP TABLE IF EXISTS carts;
CREATE TABLE carts (
	cart_id serial PRIMARY KEY,
	user_id integer NOT NULL REFERENCES users(user_id),
	session_id text NOT NULL,
	created timestamp NOT NULL,
	user_ip text NOT NULL
);

-- Cart Items Table -- 
-- Look into these urls for line id --
-- http://code.google.com/p/polarrose-postgresql-uuid/
-- http://en.wikipedia.org/wiki/UUID
-- http://www.postgresql.org/docs/current/static/datatype-uuid.html
-- Should line_id be globally unique or just unique to the cart id? Does it matter?
DROP TABLE IF EXISTS carts_items;
CREATE TABLE carts_items (
	cart_item_id serial PRIMARY KEY,
	cart_id integer NOT NULL REFERENCES carts(cart_id),
	product_id integer NOT NULL REFERENCES products(product_id),
	line_id integer NOT NULL,
	price numeric(10,2) NOT NULL,
	quantity integer NOT NULL,
	created timestamp NOT NULL,
	modified timestamp NOT NULL
);

-- Order Status Table --
CREATE TABLE order_status (
	status_id serial PRIMARY KEY,
	name text NOT NULL
);

-- Orders Table --
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
	order_id serial PRIMARY KEY,
	cart_id integer NOT NULL REFERENCES carts(cart_id),
	status_id integer NOT NULL REFERENCES order_status(status_id),
	created timestamp NOT NULL,
	modified timestamp NOT NULL
);

-- Vendors Table --
DROP TABLE IF EXISTS vendors;
CREATE TABLE vendors (
	vendor_id serial PRIMARY KEY,
	name text NOT NULL,
	created timestamp NOT NULL,
	modified timestamp NOT NULL
	-- more coming soon on this table
);

-- Purchase Order Status Table --
DROP TABLE IF EXISTS purchase_order_status;
CREATE TABLE purchase_order_status (
	status_id serial PRIMARY KEY,
	name text NOT NULL
);

-- Purchase Orders Table --
DROP TABLE IF EXISTS purchase_orders;
CREATE TABLE purchase_orders (
	purchase_order_id serial PRIMARY KEY,
	vendor_id integer NOT NULL REFERENCES vendors(vendor_id),
	order_id integer NOT NULL REFERENCES orders(order_id),
	status_id integer NOT NULL REFERENCES purchase_order_status(status_id),
	created timestamp NOT NULL,
	modified timestamp NOT NULL
);

-- Purchase Orders Items Table --
DROP TABLE IF EXISTS purchase_orders_items;
CREATE TABLE purchase_orders_items (
	purchase_order_item_id serial PRIMARY KEY,
	cart_item_id integer NOT NULL REFERENCES carts_items(cart_item_id)
);

-- Settings Table --
DROP TABLE IF EXISTS settings;
CREATE TABLE settings (
	settings_id serial PRIMARY KEY,
	"name" text NOT NULL,
	value text,
	description text,
	modified timestamp NOT NULL
)